#!/usr/bin/perl

use strict;
use Fcntl;
use DBI;
use lib 'lib';
use Encode qw(from_to);
use TS::PESBuffer;
use TS::EIT;
use TS::Descriptor;
use TS::ContentGenre;
use TS::ARIBString;

$| = 1;

my $packetsize = 1;
my $error = 0;
my $status = 0;

my $eit_start = 0;
my $eit_buf = '';
my $eit_ret = {};

while(read(STDIN, my $buf, $packetsize)) {
	my $pos = 0;
	my $sync_byte = vec($buf, $pos++, 8);

	if ($sync_byte == 0x47) {
		$packetsize = 188;

		while(length($buf) < 188 && read(STDIN, my $append, 1)) {
			$buf .= $append;
		}

		my $a = vec($buf, $pos++, 8);
		my $b = vec($buf, $pos++, 8);
		my $transport_error_indicator = $a >> 7 & 0x01;
		my $payload_unit_start_indicator = $a >> 6 & 0x01;
		my $pid = ($a & 0x1F) << 8 | $b;

		if ($transport_error_indicator) {
			$error++;
			next;
		}

		my $a = vec($buf, $pos++, 8);
		my $adaptation_field_control = $a >> 4 & 0x03;

		if ($adaptation_field_control & 0x02) {
			my $adaptation_field_length = vec($buf, $pos++, 8);
			$pos += $adaptation_field_length;
		}

		if ($pid == 0x0027) {
			if ($payload_unit_start_indicator) {
				my $pointer_field = vec($buf, $pos++, 8);

				if ($pointer_field == 0) {
					my @rets = &eit($eit_buf);

					foreach my $ret (@rets) {
						if (!defined $eit_ret->{$ret->{service_id}}) {
							$eit_ret->{$ret->{service_id}} = {};
						}

						my $prev = $eit_ret->{$ret->{service_id}}->{$ret->{section_number}};

						if (!$prev || $prev->{version_number} != $ret->{version_number}) {
							my $eit = TS::EIT->new(Binary->new($ret->{buf}));

							if (&setEvents($eit)) {
								$eit_ret->{$ret->{service_id}}->{$ret->{section_number}} = $ret;
							}
						}
					}

					$eit_start = 1;
					$eit_buf = '';
				}
			}

			if ($eit_start) {
				$eit_buf .= substr($buf, $pos);
			}
		}

		if ($status <= 0) {
			$0 = "EPG [err:$error]";
			$status = 1000;
		}

		$status--;

	} else {
		$packetsize = 1;
		$error++;
	}
}

sub eit {
	my($buf) = @_;

	my $pos = 0;
	my $len = length($buf);

	my @rets = ();

	while($pos < $len) {
		my $ret = {};

		my $table_id = vec($buf, $pos++, 8);

		my $a = vec($buf, $pos++, 8);
		my $b = vec($buf, $pos++, 8);
		my $section_length = ($a & 0x0F) << 8 | $b;
		my $program_endbyte = $pos + $section_length;

		if ($len < $program_endbyte) {
			last;
		}

		if ($table_id == 0x4E) {
			$ret->{buf} = substr($buf, $pos - 3, $section_length + 3);

			my $a = vec($buf, $pos++, 8);
			my $b = vec($buf, $pos++, 8);
			$ret->{service_id} = $a << 8 | $b;

			my $a = vec($buf, $pos++, 8);
			$ret->{version_number} = $a >> 1 & 0x1F;

			$ret->{section_number} = vec($buf, $pos++, 8);

			push(@rets, $ret);
		}

		$pos = $program_endbyte;
	}

	return @rets;
}

sub setEvents {
	my($eit) = @_;

	my $dbh = &dbiConnect();

eval {
	foreach my $event (@{$eit->{event}}) {
		&setEvent($dbh, $eit, $event);
	}

	$dbh->commit();
};

	&dbiDisconnect($dbh);

	return !$@;
}

sub setEvent {
	my($dbh, $eit, $event) = @_;

	my $event_name_char = '';
	my $text_char = '';
	my $genres = [];

	foreach my $descriptor (@{$event->{descriptor}}) {
		if ($descriptor->{descriptor_tag} == TS::Descriptor::DESC_SHORT_EVENT) {
			$event_name_char = TS::ARIBString::utf8($descriptor->{event_name_char});
			$text_char = TS::ARIBString::utf8($descriptor->{text_char});
		} elsif ($descriptor->{descriptor_tag} == TS::Descriptor::DESC_CONTENT) {
			$genres = $descriptor->{genre};
		}
	}

	if ($event->{start_time} eq '2038-04-22 ff:ff:ff' || $event->{duration} eq 'ff:ff:ff') {
		return;
	}

	my $sql = <<END;
select
	dv_id
from
	da_event
where
	dv_service_id = ?
and
	dv_original_network_id = ?
and
	? <= dv_start_time
and
	dv_start_time < datetime(?, ?)
END

	my $sth = $dbh->prepare($sql);
	my $rv = $sth->execute(
		$eit->{service_id},
		$eit->{original_network_id},
		$event->{start_time},
		$event->{start_time},
		$event->{duration},
	);

	while (my $ref = $sth->fetch()) {
		my($dv_id) = @$ref;

		my $ddh = $dbh->prepare('delete from da_event where dv_id = ?');
		my $rv = $ddh->execute($dv_id);

		my $ddh = $dbh->prepare('delete from da_genre where dv_id = ?');
		my $rv = $ddh->execute($dv_id);
	}

	$sth->finish();

	my $sql = <<END;
update
	da_event
set
	dv_duration = time(julianday(?) - julianday(dv_start_time) - 0.5)
where
	dv_service_id = ?
and
	dv_original_network_id = ?
and
	dv_start_time < ?
and
	? < datetime(dv_start_time, dv_duration)
END

	my $udh = $dbh->prepare($sql);
	my $rv = $udh->execute(
		$event->{start_time},
		$eit->{service_id},
		$eit->{original_network_id},
		$event->{start_time},
		$event->{start_time},
	);

	my $sql = <<END;
insert into
	da_event
(
	dv_service_id,
	dv_transport_stream_id,
	dv_original_network_id,
	dv_event_id,
	dv_start_time,
	dv_duration,
	dv_name,
	dv_text
) values (
	?,
	?,
	?,
	?,
	?,
	?,
	?,
	?
)
END

	my $ish = $dbh->prepare($sql);
	my $rv = $ish->execute(
		$eit->{service_id},
		$eit->{transport_stream_id},
		$eit->{original_network_id},
		$event->{event_id},
		$event->{start_time},
		$event->{duration},
		$event_name_char,
		$text_char,
	);

	my $sql = <<END;
select
	dv_id
from
	da_event
where
	dv_service_id = ?
and
	dv_original_network_id = ?
and
	dv_start_time = ?
END

	my $sth = $dbh->prepare($sql);
	my $rv = $sth->execute(
		$eit->{service_id},
		$eit->{original_network_id},
		$event->{start_time},
	);

	my $ref = $sth->fetch() || die "event no match: $event->{start_time}\n";
	my $dv_id = $ref->[0];

	$sth->finish();

	foreach my $genre (@$genres) {

		my $sql = <<END;
insert into
	da_genre
(
	dv_id,
	mg_level1,
	mg_level2
) values (
	?,
	?,
	?
)
END

		my $ish = $dbh->prepare($sql);
		my $rv = $ish->execute(
			$dv_id,
			$genre->{content_nibble_level_1},
			$genre->{content_nibble_level_2},
		);
	}
}

sub dbiConnect {
	my $dsn = 'DBI:SQLite:dbname=epg.db';
	my $dbh = DBI->connect($dsn, undef, undef, {RaiseError => 1, AutoCommit => 0});
	return $dbh;
}

sub dbiDisconnect {
	my($dbh) = @_;
	$dbh->disconnect();
}

